package cn.edu.glut.user.repository.impl;


import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.hibernate.type.StandardBasicTypes;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import cn.edu.glut.entity.Student;
import cn.edu.glut.repository.AbstractBatchRepository;
import cn.edu.glut.vo.StudentToAdminVO;
/**
 * 
 * @author Caizhfy
 * @version v.0.1
 * @createTime 2017年12月15日下午12:12:04
 * @email Caizhfy@163.com
 * @description 由于需要实现动态查询与报表类查询，所以扩展自定义查询
 *
 */
@Repository
public class StudentRepositoryImpl extends AbstractBatchRepository<Student>{
	@PersistenceContext
	private EntityManager em;
	final Logger logger = LoggerFactory.getLogger(StudentRepositoryImpl.class);

	//自定义动态报表原生sql查询
	//只要isSelect不为空，那么就执行条件查询：前端自行根据返回值判断是否已经选题(大于0就是选了题的)
	public Page<StudentToAdminVO> findAllStudentToAdminVOPage(Pageable pageable, String majorId, Boolean isSelect, Boolean isSelectSuccess) {
		//设置条件
		StringBuffer where = new StringBuffer(" where 1=1 ");
		StringBuffer having = new StringBuffer(" having 1=1");
		if(!StringUtils.isEmpty(majorId)){
			where.append(" and s.major_id=:majorId ");
		}
		if(isSelect!=null){
			//是否选题了，只需要看查出的这个数是否大于零即可
			if(isSelect)
				having.append(" and count(se.id)>0 ");
			else
				having.append(" and count(se.id)=0 ");
		}
		if(isSelectSuccess != null){
			if(isSelectSuccess)
				having.append(" and max(se.is_select)>0");
			else
				having.append(" and (max(se.is_select) is null or max(se.is_select)<=0)");
		}
		//主体sql
		String sql = "select s.id, s.username, s.nickname, s.sclass, m.name majorName, count(se.id) as choose, max(se.is_select) as selectSuccess from student s"
				+ " left join selection se on s.id=se.student_id "
				+ " left join major m on m.id=s.major_id "
				+ where
				+ " group by s.id" 
				+ having;
		String countSql = null;
		//计算总记录数sql
		if(isSelect!=null){
			countSql = "select count(*) from student s " 
					+ where 
					+ " and s.id in(select ss.id FROM student ss left join selection se on se.student_id=ss.id GROUP BY ss.id "
					+ having
					+ " )";
		}else{
			countSql = "select count(*) from student s " + where;
		}
		//创建原生查询
		Query query = em.createNativeQuery(sql);
		Query countQuery = em.createNativeQuery(countSql);
		if(!StringUtils.isEmpty(majorId)){
			query.setParameter("majorId", majorId);
			countQuery.setParameter("majorId", majorId);
		}
		int total = Integer.valueOf(countQuery.getSingleResult().toString());
//		pageable.getPageNumber()==0 ? pageable.getOffset() : pageable.getOffset()-5
		if(pageable!=null){
			query.setFirstResult(pageable.getOffset());
			query.setMaxResults(pageable.getPageSize());
		}
		//对象映射
		query.unwrap(SQLQuery.class)
			.addScalar("id", StandardBasicTypes.STRING)
			.addScalar("username", StandardBasicTypes.STRING)
			.addScalar("nickname", StandardBasicTypes.STRING)
			.addScalar("sclass", StandardBasicTypes.STRING)
			.addScalar("majorName", StandardBasicTypes.STRING)
			.addScalar("choose", StandardBasicTypes.INTEGER)
			.addScalar("selectSuccess", StandardBasicTypes.INTEGER)
			.setResultTransformer(Transformers.aliasToBean(StudentToAdminVO.class));
		
		return new PageImpl<StudentToAdminVO>(query.getResultList(), pageable, total);
	}
}
